package ch.fhnw.edu.rental.daos.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import ch.fhnw.edu.rental.daos.MovieDAO;
import ch.fhnw.edu.rental.daos.PriceCategoryDAO;
import ch.fhnw.edu.rental.model.Movie;

public class JdbcMovieDAO extends SimpleJdbcDaoSupport implements MovieDAO {

	private PriceCategoryDAO priceCategoryDAO;

	public void setPriceCategoryDAO(PriceCategoryDAO priceCategoryDAO) {
		this.priceCategoryDAO = priceCategoryDAO;
	}

	@Override
	public Movie getById(Long id){
		SimpleJdbcTemplate template = getSimpleJdbcTemplate();
		Map<String, Object> res = template.queryForMap("select * from MOVIES where MOVIE_ID = ?", new Object[]{id});
		long priceCategory = (Long)res.get("PRICECATEGORY_FK");
		Movie m = new Movie((String)res.get("MOVIE_TITLE"),
		                    (java.sql.Timestamp)res.get("MOVIE_RELEASEDATE"),
		                    priceCategoryDAO.getById(priceCategory)
		                    );
		m.setId((Long)res.get("MOVIE_ID"));
		m.setRented((Boolean)res.get("MOVIE_RENTED"));
		return m;
	}

	@Override
	public List<Movie> getByTitle(String name) {
		SimpleJdbcTemplate template = getSimpleJdbcTemplate();
		return template.query("select * from MOVIES where MOVIE_TITLE = ?",
							  new RowMapper<Movie>(){
							  	public Movie mapRow(ResultSet rs, int row) throws SQLException {
									return createMovie(rs);
							  	}
							  },
							  name
							  );
	}

	@Override
	public List<Movie> getAll() {
		SimpleJdbcTemplate template = getSimpleJdbcTemplate();
		return template.query("select * from MOVIES",
							  new RowMapper<Movie>(){
							  	public Movie mapRow(ResultSet rs, int row) throws SQLException {
									return createMovie(rs);
							  	}
							  }
							  );
	}
	
	@Override
	public void saveOrUpdate(Movie movie) {
		SimpleJdbcTemplate template = getSimpleJdbcTemplate();
		if(movie.getId() == null){
			//insert
		} else {
			//update
			template.update("UPDATE MOVIES SET MOVIE_TITLE = ?," +
					        "MOVIE_RENTED = ?," +
					        "MOVIE_RELEASEDATE = ?," +
					        "PRICECATEGORY_FK = ?" +
					        "WHERE MOVIE ID = ?",
					        movie.getTitle(),
					        movie.isRented(),
					        movie.getReleaseDate(),
					        movie.getPriceCategory().getId(),
					        movie.getId()
					        );
		}
//		Connection conn = null;
//		try {
//			conn = getDataSource().getConnection();
//
//			PreparedStatement pst;
//			if(movie.getId() == null){	// insert
//				long id = 0;
//				Statement st = conn.createStatement();
//				ResultSet rs = st.executeQuery("select max(MOVIE_ID) from MOVIES");
//				if(rs.next()){id = rs.getLong(1)+1;}
//				movie.setId(id);
//				
//				pst = conn.prepareStatement("INSERT INTO MOVIES (MOVIE_ID, MOVIE_TITLE, MOVIE_RELEASEDATE, MOVIE_RENTED, PRICECATEGORY_FK) VALUES (?,?,?,?,?)");
//				pst.setLong(1, id);
//				pst.setString(2, movie.getTitle());
//				pst.setDate(3, new Date(movie.getReleaseDate().getTime()));
//				pst.setBoolean(4, movie.isRented());
//				pst.setLong(5, movie.getPriceCategory().getId());
//				pst.execute();
//			}
//			else {	// update
//				pst = conn.prepareStatement("UPDATE MOVIES SET MOVIE_TITLE=?, MOVIE_RELEASEDATE=?, MOVIE_RENTED=?, PRICECATEGORY_FK=? where MOVIE_ID=?");
//				pst.setLong(5, movie.getId());
//				pst.setString(1, movie.getTitle());
//				pst.setDate(2, new Date(movie.getReleaseDate().getTime()));
//				pst.setBoolean(3, movie.isRented());
//				pst.setLong(4, movie.getPriceCategory().getId());
//				pst.execute();
//			}
//		} catch (SQLException e) {
//			throw new RuntimeException(e);
//		}
//		finally {
//			if(conn != null){
//				try {
//					conn.close();
//				} catch (SQLException e) {
//					throw new RuntimeException(e);
//				}
//			}
//		}
	}
	
	@Override
	public void delete(Movie movie) {
		Connection conn = null;
		try {
			conn = getDataSource().getConnection();
			Statement st = conn.createStatement();
			st.execute("delete from MOVIES where MOVIE_ID = " + movie.getId());
			movie.setId(null);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}
	
	private Movie createMovie(ResultSet rs) throws SQLException {
		long priceCategory = rs.getLong("PRICECATEGORY_FK");
		Movie m = new Movie(
				rs.getString("MOVIE_TITLE"), 
				rs.getDate("MOVIE_RELEASEDATE"),
				priceCategoryDAO.getById(priceCategory));
		m.setId(rs.getLong("MOVIE_ID"));
		m.setRented(rs.getBoolean("MOVIE_RENTED"));
		return m;
	}

}
